package org.PropertyManageMentSystem.Manager;

import org.PropertyManageMentSystem.Data.*;


import java.sql.*;
import java.util.ArrayList;

public class WorkerManager {
    private Connection conn;

    public WorkerManager(MysqlConfig config) throws SQLException, ClassNotFoundException {
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            //创建连接
            conn = DriverManager.getConnection(config.getUrl(), config.getUser(), config.getPassword());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Long addWorker(String name, String tel, ArrayList<EmergencyType> types, Worker.WorkerState state) throws Exception {
        Long workerId = findWorker(name, tel);
        if (workerId == null) {
            String regex = "[1][34578][0-9]{9}";
            if (!tel.matches(regex)) {
                throw new Exception("电话号码格式错误");
            }
            else {
                String sql = "insert into worker (name, tel, state) values(?,?,?)";
                PreparedStatement pst = conn.prepareStatement(sql);

                pst.setString(1, name);
                pst.setString(2, tel);
                pst.setLong(3, state.ordinal());

                pst.executeUpdate();
                pst.close();

                workerId = findWorker(name, tel);

                for (EmergencyType emergencyType:types) {
                    Long emergencytypeId = findEmergencytypeIdByEmergencyType(emergencyType);
                    if (emergencytypeId != null) {
                        addWorkerEmergencyTypeRelation(workerId, emergencytypeId);
                    }
                }
            }
        }
        return workerId;
    }

    private void addWorkerEmergencyTypeRelation(Long workerId, Long emergencyTypeId) {
        String sql = "insert into WorkerEmergencyTypeRelation (workerId, emergencyTypeId) values(?,?)";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setLong(1, workerId);
            pst.setLong(2, emergencyTypeId);
            pst.executeUpdate();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Long getWorkerByIdTel(String name, String tel) throws SQLException {
        return findWorker(name, tel);
    }

    private Long findWorker(String name, String tel) throws SQLException {
        String sql="select * from worker where name=? and tel=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1, name);
        pst.setString(2, tel);
        ResultSet rs = pst.executeQuery();
        if (rs.next()) {
            return rs.getLong("workerId");
        }
        else {
            return null;
        }
    }

    private Long findEmergencytypeIdByEmergencyType(EmergencyType type) throws SQLException {
        String sql="select * from emergencytype where typename=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1, type.getName());
        ResultSet rs = pst.executeQuery();
        if (rs.next()) {
            return  rs.getLong("typeId");
        }
        else {
            return null;
        }
    }

    private EmergencyType findEmergencytypeById(Long id) throws SQLException {
        String sql="select * from emergencytype where typeId=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setLong(1, id);
        ResultSet rs = pst.executeQuery();
        if (rs.next()) {
            return new EmergencyType(id, rs.getString("typename"));
        }
        else {
            return null;
        }
    }

    public ArrayList<Worker> getWorkersByType(EmergencyType type) throws Exception {
        String sql="select * from WorkerEmergencyTypeRelation where emergencytypeId=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        Long emergencytypeId = findEmergencytypeIdByEmergencyType(type);
        if (emergencytypeId==null){
            throw new Exception("找不到EmergencyType的Id");
        }
        pst.setLong(1, emergencytypeId);
        ResultSet rs = pst.executeQuery();
        ArrayList<Worker> workers = new ArrayList<>();
        while (rs.next()){
            workers.add(getWorkerById(rs.getLong("workerId")));
        }
        if (workers.isEmpty()) {
            return null;
        }
        else {
            return workers;
        }
    }

    public Worker getWorkerById(Long workerId) throws Exception {
        String sql="select * from worker where workerId=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setLong(1, workerId);
        ResultSet rs = pst.executeQuery();
        rs.next();
        return new Worker(workerId, rs.getString("name"), rs.getString("tel"),
                Worker.WorkerState.values()[rs.getInt("state")]);
    }

    public void changeWorkerStateById(Long workerId, Worker.WorkerState state) throws SQLException {
        String sql="update worker set state=? where workerId=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setLong(2, workerId);
        pst.setInt(1, state.ordinal());
        pst.executeUpdate();
    }

    public ArrayList<Worker> getAllWorker() throws SQLException {
        String sql="select * from worker";
        PreparedStatement pst=conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        ArrayList<Worker> workers = new ArrayList<>();
        while (rs.next()) {
            Worker worker = new Worker(rs.getLong("workerId"),rs.getString("name"),
                                        rs.getString("tel"),
                                        Worker.WorkerState.values()[rs.getInt("state")]);
            workers.add(worker);
        }
        return workers;
    }
}
